using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Shatters : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataTable GetTypeSubtotalByCondition(ShatterCondition sc)
		{
			string text = "SELECT si.Department as 工作组, pt.[Name] AS 成品名称, si.TransitionThickness as 补片层名称, sum(si.Quantity) AS 补片数量,sum(dbo.ItemSquare(p.Width,p.Height,si.Quantity)) AS 补片面积 FROM ShatterItem as si inner join Product as p on si.ProductID=p.PKID inner join Shatter as s on si.ShatterID=s.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			this.selectCommand = new SqlCommand();
			string text2 = "";
			if (sc.DateStart != null)
			{
				if (text2 == "")
				{
					text2 += "[Date]>=CONVERT(DATETIME,@DateStart) AND [Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				else
				{
					text2 += " AND [Date]>=CONVERT(DATETIME,@DateStart) AND [Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateStart"].Value = sc.DateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateEnd"].Value = sc.DateEnd;
			}
			if (sc.Locked > -1)
			{
				if (text2 == "")
				{
					text2 += "s.Locked=@Locked";
				}
				else
				{
					text2 += " AND s.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = sc.Locked;
			}
			if (sc.ShatterNumber != null)
			{
				if (text2 == "")
				{
					text2 += "ShatterNumber LIKE '%'+@ShatterNumber+'%'";
				}
				else
				{
					text2 += " AND ShatterNumber LIKE '%'+@ShatterNumber+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ShatterNumber"].Value = sc.ShatterNumber.Replace(" ", "%");
			}
			if (sc.Remark != null)
			{
				if (text2 == "")
				{
					text2 += "s.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text2 += " AND s.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = sc.Remark.Replace(" ", "%");
			}
			if (text2 != "")
			{
				text2 = " WHERE " + text2;
			}
			text = text + text2 + " group by si.Department, pt.[Name], si.TransitionThickness order by pt.[Name]";
			DataTable dataTable = new DataTable("TypeSubtotal");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetTypeSubtotalByCondition_2(ShatterCondition sc)
		{
			string text = "SELECT si.Department as 工作组,sum(dbo.ItemSquare(p.Width,p.Height,si.Quantity)) AS 面积,si.TransitionThickness as  补片厚度,sum(dbo.ItemSquare(p.Width,p.Height,si.Quantity)*1) as 补片总面积,sum(dbo.RecoupSquareOfItem(p.Width,p.Height,si.Quantity,si.RecoupPercent)) as 扣薪面积,convert(decimal(12,4),sum(dbo.RecoupSquareOfItem(p.Width,p.Height,si.Quantity,si.RecoupPercent)*si.TransitionThickness/8)) as 折合面积 FROM ShatterItem as si inner join Product as p on si.ProductID=p.PKID inner join Shatter as s on si.ShatterID=s.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			this.selectCommand = new SqlCommand();
			string text2 = "";
			if (sc.DateStart != null)
			{
				if (text2 == "")
				{
					text2 += "[Date]>=CONVERT(DATETIME,@DateStart) AND [Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				else
				{
					text2 += " AND [Date]>=CONVERT(DATETIME,@DateStart) AND [Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateStart"].Value = sc.DateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateEnd"].Value = sc.DateEnd;
			}
			if (sc.Locked > -1)
			{
				if (text2 == "")
				{
					text2 += "s.Locked=@Locked";
				}
				else
				{
					text2 += " AND s.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = sc.Locked;
			}
			if (sc.ShatterNumber != null)
			{
				if (text2 == "")
				{
					text2 += "ShatterNumber LIKE '%'+@ShatterNumber+'%'";
				}
				else
				{
					text2 += " AND ShatterNumber LIKE '%'+@ShatterNumber+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ShatterNumber"].Value = sc.ShatterNumber.Replace(" ", "%");
			}
			if (sc.Remark != null)
			{
				if (text2 == "")
				{
					text2 += "s.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text2 += " AND s.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = sc.Remark.Replace(" ", "%");
			}
			if (text2 != "")
			{
				text2 = " WHERE " + text2;
			}
			text = text + text2 + " group by si.Department, si.TransitionThickness order by si.Department,si.TransitionThickness";
			DataTable dataTable = new DataTable("TypeSubtotal");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool UpdateRemark(string sShatterNumber, string sNewRemark)
		{
			string cmdText = "UPDATE [Shatter] SET Remark=@Remrk WHERE ShatterNumber=@ShatterNumber";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Remrk", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				this.updateCommand.Parameters["@Remrk"].Value = sNewRemark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public int GetPrintedQuantity(string sShatterNumber)
		{
			string cmdText = "SELECT PrintedQuantity from [Shatter] where ShatterNumber=@ShatterNumber";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				object obj = this.selectCommand.ExecuteScalar();
				if (obj != null)
				{
					result = Convert.ToInt32(obj);
				}
				else
				{
					result = -1;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool PrintOnce(string sShatterNumber)
		{
			string cmdText = "Update [Shatter] set PrintedQuantity=PrintedQuantity+1 where ShatterNumber=@ShatterNumber";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				this.updateCommand.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool LockData(string sShatterNumber)
		{
			string cmdText = "UPDATE [Shatter] SET Locked=1 WHERE ShatterNumber=@ShatterNumber";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UnLockData(string sShatterNumber)
		{
			string cmdText = "UPDATE [Shatter] SET Locked=0 WHERE ShatterNumber=@ShatterNumber";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable LoadShatterByShatterNumber(string sShatterNumber)
		{
			DataTable dataTable = new DataTable("Shatter");
			string cmdText = "SELECT s.PKID AS '序号',s.ShatterNumber AS '补片单号',s.[Date] AS '日期',s.Locked AS '是否锁定',s.Remark AS '备注',op.[Name] AS '操作者' FROM [Shatter] AS s INNER JOIN Operator AS op ON s.OperatorID=op.PKID WHERE s.ShatterNumber=@ShatterNumber";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetSumUpByCondition(ShatterCondition sc)
		{
			DataTable dataTable = new DataTable("SumUp");
			string text = "SELECT s.PKID as 序号,s.ShatterNumber as 补片单号,s.[Date] as 补片日期,(select sum(si.Quantity) from ShatterItem as si inner join Shatter as s1 on si.ShatterID=s1.PKID where s1.ShatterNumber=s.ShatterNumber) as 补片数量,(select sum(dbo.ItemSquare(p.Width,p.Height,si.Quantity)) from ShatterItem as si inner join Product as p on si.ProductID=p.PKID inner join Shatter as s1 on si.ShatterID=s1.PKID where s1.ShatterNumber=s.ShatterNumber) as 面积,s.Locked as 锁定, s.Remark as 备注,op.[Name] as 操作者,s.PrintedQuantity as 打印次数 from Shatter as s inner join Operator as op on s.OperatorID=op.PKID";
			this.selectCommand = new SqlCommand();
			string text2 = "";
			if (sc.DateStart != null)
			{
				if (text2 == "")
				{
					text2 += "[Date]>=CONVERT(DATETIME,@DateStart) AND [Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				else
				{
					text2 += " AND [Date]>=CONVERT(DATETIME,@DateStart) AND [Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateStart"].Value = sc.DateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateEnd"].Value = sc.DateEnd;
			}
			if (sc.Locked > -1)
			{
				if (text2 == "")
				{
					text2 += "s.Locked=@Locked";
				}
				else
				{
					text2 += " AND s.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = sc.Locked;
			}
			if (sc.ShatterNumber != null)
			{
				if (text2 == "")
				{
					text2 += "ShatterNumber LIKE '%'+@ShatterNumber+'%'";
				}
				else
				{
					text2 += " AND ShatterNumber LIKE '%'+@ShatterNumber+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ShatterNumber"].Value = sc.ShatterNumber.Replace(" ", "%");
			}
			if (sc.Remark != null)
			{
				if (text2 == "")
				{
					text2 += "s.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text2 += " AND s.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = sc.Remark.Replace(" ", "%");
			}
			if (text2 != "")
			{
				text2 = " WHERE " + text2;
			}
			text = text + text2 + " ORDER BY s.[Date]";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public string InsertShatter(string sShatterNumber, string sRemark, int nOperatorID)
		{
			string result = null;
			try
			{
				this.insertCommand = new SqlCommand("InsertShatter", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@ShatterNumber", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters["@ShatterNumber"].Value = sShatterNumber;
				this.insertCommand.Parameters["@ShatterNumber"].Direction = ParameterDirection.InputOutput;
				this.insertCommand.Parameters.Add(new SqlParameter("@remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@remark"].Value = sRemark;
				this.insertCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
				result = this.insertCommand.Parameters["@ShatterNumber"].Value.ToString();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
